<?php


namespace app\admin\controller;

use think\App;
use think\facade\Db;
use app\BaseController;
use think\validate\ValidateRule;
use think\facade\View;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\cell\Coordinate;

class Travelroute extends BaseController
{
    public function __construct(App $app)
    {
        parent::__construct($app);
        $this->checkLogin();
        View::assign('left', 'travel');
    }
    public function form1()
    {
        $get = input('get.');

        if (empty($get)) {
            $list = Db::name('trip')
                ->alias('a')
                ->leftJoin('daoyou b', 'a.d_id=b.id')
                ->field('a.*,b.dyname,b.dyiphone,b.zhengjian')
                ->select();
        } else {
            $name = $get['name'];
            $list = Db::name('trip')
                ->alias('a')
                ->leftJoin('daoyou b', 'a.d_id=b.id')
                ->where('a.travelroute', 'like', "%{$name}%")
                ->field('a.*,b.dyname,b.dyiphone,b.zhengjian')
                ->select();

        }
        return view('', [
            'list' => $list,
        ]);
    }

    public function edit()
    {
        if ($id = input('get.')) {
            $id = intval($id['id']);
            $list = Db::name('trip')->where('id',$id)->find();
        } else {
            $post = input('post.');
            $list = Db::name('trip')->where('id', $post['id'])->find();
            if (!empty($list)) {
                $p1 = Db::name('trip')->where(array('id' => $list['id']))->data([
                    'datetime' => $post['datetime'],
                    'sjname' => $post['sjname'],
                    'sjiphone' => $post['sjiphone'],
                    'sjzhengjian' => $post['sjzhengjian'],
                    'groupNum' => $post['groupNum'],
                    'travelroute' => $post['travelroute'],
                    'carNum' => $post['carNum'],
                    'teamsNum' => $post['teamsNum'],
                    'adultsNum' => $post['adultsNum'],
                    'minorsNum' => $post['minorsNum'],
                    'address' => $post['address'],
                    'country' => $post['country'],
                    'carCompany' => $post['carCompany'],
                    'teamtype' => $post['teamtype'],
                    'studenteamtype' => $post['studenteamtype'],
                    'YNcar' => $post['YNcar'],
                    'needcar' => $post['needcar']
                ])->update();

//                Db::name('daoyou')->where(array('id' => $list['id']))->update([
//                    'dyname' => $post['dyname'],
//                    'dyiphone' => $post['dyiphone'],
//                    'zhengjian' => $post['zhengjian']
//                ]);
                // var_dump($p1);echo '<hr />'; var_dump($p2);echo '<hr />'; var_dump($p3);echo '<hr />';die;
                $this->success('修改成功', '/admin.php/Travelroute/form1');
            } else {
                $this->error('修改失败', '/admin.php/Travelroute/edit?id='.$post['id']);
            }
        }
        return view('', [
            'list' => $list,
        ]);
        return view();
    }

    public function del()
    {
        $id = $_GET['id'];
        $list = Db::name('trip')
            ->where('id', $id)
            ->delete();
        return view();
    }

    /**
     * 从数据库导出数据到表格
     * @param array $yklist 游客数据
     * @param array $list 行程及导游司机的数据
     * @param array $keys 要获取的内容键名
     * @param string $filename 导出的文件名
     */
    public function export()
    {
        $id = input('get.id');
        //获取数据库的数据。
        $List = Db::name('daoyou')
            ->alias("a")
            ->join('trip b','a.id = b.d_id')
            ->where('a.id',$id)->select();
        $newExcel = new Spreadsheet();  //创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();  //获取当前操作sheet的对象
        $objSheet->setTitle('行程');  //设置当前sheet的标题

        //设置宽度为true,不然太窄了
        $newExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
        $newExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);
        //设置第一栏的标题

        $objSheet->setCellValue('A1', '成行团号')
            ->setCellValue('B1', '出团时间')
            ->setCellValue('C1', '客源国家')
            ->setCellValue('D1', '接车地址')
            ->setCellValue('E1', '团队人数')
            ->setCellValue('F1', '成年人数')
            ->setCellValue('G1', '未成年人数')
            ->setCellValue('H1', '团队类型')
            ->setCellValue('I1', '是否是学生团')

            ->setCellValue('A3', '导游姓名')
            ->setCellValue('B3', '导游证号')
            ->setCellValue('C3', '导游手机')
            ->setCellValue('D3', '汽车公司')
            ->setCellValue('E3', '是否汽车公司提供')
            ->setCellValue('F3', '用车需求')
            ->setCellValue('G3', '车牌号')
            ->setCellValue('H3', '司机姓名')
            ->setCellValue('I3', '司机电话')
            ->setCellValue('J3', '司机从业资格证')
        ;

        foreach ($List as $k => $val) {

            $objSheet->setCellValue('A' . $k+2, $val['groupNum'])
                ->setCellValue('B' . $k+2, date('Y-m-d H:i:s', $val['datetime']))
                ->setCellValue('C' . $k+2, $val['country'])
                ->setCellValue('D' . $k+2,$val['address'])
                ->setCellValue('E' . $k+2,$val['teamsNum'])
                ->setCellValue('F' . $k+2,$val['adultsNum'])
                ->setCellValue('G' . $k+2,$val['minorsNum'])
                ->setCellValue('H' . $k+2,$val['teamtype'])
                ->setCellValue('I' . $k+2,$val['studenteamtype'])

                ->setCellValue('A' . $k+4,$val['dyname'])
                ->setCellValue('B' . $k+4,$val['zhengjian'])
                ->setCellValue('C' . $k+4,$val['dyiphone'])
                ->setCellValue('D' . $k+4,$val['carCompany'])
                ->setCellValue('E' . $k+4,$val['YNcar'])
                ->setCellValue('F' . $k+4,$val['needcar'])
                ->setCellValue('G' . $k+4,$val['carNum'])
                ->setCellValue('H' . $k+4,$val['sjname'])
                ->setCellValue('I' . $k+4,$val['sjiphone'])
                ->setCellValue('J' . $k+4,$val['sjzhengjian'])
            ;
        }
        $yklist=Db::name('daoyou')
            ->alias('a')
            ->join('contract b','a.id=b.d_id')
            ->join('youke c', 'b.homeNum=c.home_num')
            ->where('a.id',$id)
            ->field('c.*')
            ->select();
        $objSheet->setCellValue('A7', '姓名')
            ->setCellValue('B7', '性别')
            ->setCellValue('C7', '证件类型')
            ->setCellValue('D7', '证件号码')
            ->setCellValue('E7', '生日')
            ->setCellValue('F7', '电话')
            ->setCellValue('G7', '国籍')
            ->setCellValue('H7', '常住地址')
            ->setCellValue('I7', '常住地址')
        ;
        foreach ($yklist as $k => $val) {
            $k = $k + 8;
            $objSheet->setCellValue('A' . $k, $val['ykname'])
                ->setCellValue('B' . $k, $val['sex'])
                ->setCellValue('C' . $k, $val['sfzhengjiantype'])
                ->setCellValue('D' . $k,$val['sfzhengjian'])
                ->setCellValue('E' . $k,$val['birthday'])
                ->setCellValue('F' . $k,$val['ykiphone'])
                ->setCellValue('G' . $k,$val['ykcountry'])
                ->setCellValue('H' . $k,$val['Raddress'])
                ->setCellValue('H' . $k,$val['home_num'])
            ;
        }

        $this->downloadExcel($newExcel, '管理员表', 'Xls');
    }



    /**
     * 下载数据EXECL表格导出
     * 'newExcel'   => 'EXECL表格',
     * 'filename' => '文件名称',
     * 'format'=> 文件后缀,
     */
    function downloadExcel($newExcel, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }
        header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($newExcel, $format);
        $objWriter->save('php://output');
        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');
        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        //If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
        exit;
    }
}